import json
from datetime import datetime
import requests
from urllib.parse import urljoin
import pymysql
#深圳海事监管服务平台-深圳海事监管服务平台-任务管理-融合后任务-历史任务-船载危险货物申报员或装箱检查员从业人员和从业单位监督检查

# 发送目标请求
BASE_URL = "http://191.254.1.78/msaServiceUrl/taskInfoRestService/selectByConditions"
headers = {
    "Content-Type": "application/json",
    #"Authorization": "Bearer 6a0a7d0f-c768-4121-ae36-5bd87a895e18"
}

cookies = {
}

data = {
'accessToken': "04961185-faab-442f-bbbe-a5add66d9a01",
'pageNum': 1,
'pageSize': 10000,
"parameterJson": "{\"orgId\":\"\",\"isShipType\":\"\",\"taskType\":\"RC_00000006\",\"status\":\"5,8,11\",\"decision\":\"\",\"startTime\":\"2024-10-01 00:00:00\",\"endTime\":\"2024-12-31 23:59:59\",\"startTimeUp\":\"\",\"endTimeUp\":\"\",\"taskGoalName\":\"\",\"taskName\":\"\",\"lawName\":\"\",\"ifSpecial\":\"\",\"deptCode\":\"\",\"taskOrigin\":\"history\"}",
'platform':"PC",
'projectSu': "SZMSA",
"requestSource": "layui"
}

#组装请求配置
session = requests.Session()

#请求指标11船舶污染物的接收和处理情况备案数量
response_lo = session.post(BASE_URL, headers=headers, json=data, cookies=cookies)

#响应回来的参数
#print("响应回来的参数:",response_lo.text)
#响应回来的参数文本转json
strData = json.loads(response_lo.text)
strRecordsData = strData['datas']['data'] #获取到具体数据的位置

#获取数据情况records、total、size、current、pages
print("获取："+str(strData['datas']['count'])+"条数据")

#打印每一列数据的字段key
#print("获取建表字段：" , tuple(strRecordsData[0].keys())  )

if response_lo.status_code == 200:
    # 数据库连接配置
    db_config = {
        'host': '191.254.8.220',
        'user': 'root',
        'password': 'Szmsa@520',
        'db': 'spiders',
        'charset': 'utf8mb4',
        'cursorclass': pymysql.cursors.DictCursor
    }
    # 表名和字段名
    table_name = 'ywtg_ship_xianchangkaixiangjiancha_39_40_42'

    # 连接数据库
    connection = pymysql.connect(**db_config)

try:
    # 开始事务
    with connection.cursor() as cursor:
        # 循环插入每条记录
        for record in strRecordsData:
            # 获取需要插入的字段名称
            fields = ', '.join(record.keys())
            # 判断是否包含 "index"，如果包含则替换为 "index1"
            if "index" in fields:
                fields = fields.replace("index", "index1")
            # 拼装需要插入字段对应的%号个数
            values = ", ".join(['%s'] * len(record))
            # 判断是否包含 "None"，如果包含则替换为 "null"
            if "None" in values:
                values = values.replace("None", "null")

            # 构建插入语句，插入业务主表
            insert_stmt = (
                "INSERT INTO `{table}` ({fields}) "
                "VALUES ({values})"
            ).format(
                table=table_name,
                fields=fields,
                values=values
            )
            # 插入语句
            #print("插入SQl语句：", insert_stmt)
            #  print("插入值：", tuple(record.values()))
            print("插入行政处罚主表表:", tuple(record.values()))
            # 执行sql语句插入数据，record.values()用于获取字段中的值
            cursor.execute(insert_stmt, tuple(record.values()))

    # 提交事务
    connection.commit()
finally:
    # 关闭数据库连接
    connection.close()



